package com.zm.demo.mysql.connect;

import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Random;

public class ExcuteDemo {

    static String url = "jdbc:mysql://localhost:3306/local1?user=root&password=root&characterEncoding=utf8&useSSL=false";

    public static void main(String[] args) throws Exception {
        Long start = System.currentTimeMillis();
        executeForSelect();
        System.out.println("executeForSelect 耗时： " + (System.currentTimeMillis() - start) + " ms \n");

        start = System.currentTimeMillis();
        executeForUpdate();
        System.out.println("executeForUpdate 耗时： " + (System.currentTimeMillis() - start) + " ms \n");

        start = System.currentTimeMillis();
        executeQuery();
        System.out.println("executeQuery 耗时： " + (System.currentTimeMillis() - start) + " ms \n");

        start = System.currentTimeMillis();
        executeUpdate();
        System.out.println("executeUpdate 耗时： " + (System.currentTimeMillis() - start) + " ms \n");
    }

    private static void executeForSelect() throws Exception{
        Connection connection = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            connection = DriverManager.getConnection(url);
            statement = connection.createStatement();
            statement.setFetchSize(Integer.MIN_VALUE);
            String sql = "select * from user";
            if (statement.execute(sql)){
                rs = statement.getResultSet();
            }
            int count=0;
            while (rs.next()){
                count++;
            }
            System.out.println("executeForSelect count: " + count);
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            close(connection,statement,rs);
        }
    }

    private static void executeForUpdate() throws Exception{
        Connection connection = null;
        Statement statement = null;
        int updateCount = 0;
        try {
            connection = DriverManager.getConnection(url);
            statement = connection.createStatement();
            String sql = "update user set name='啊啊啊' where id = " + new Random().nextInt(999999);
            if (!statement.execute(sql)){
                updateCount = statement.getUpdateCount();
            }
            System.out.println("executeForUpdate updateCount: " + updateCount);
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            close(connection,statement,null);
        }
    }

    private static void executeQuery() throws Exception{
        Connection connection = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            connection = DriverManager.getConnection(url);
            statement = connection.createStatement();
            statement.setFetchSize(Integer.MIN_VALUE);
            String sql = "select * from user";
            rs = statement.executeQuery(sql);
            int count=0;
            while (rs.next()){
                count++;
            }
            System.out.println("executeQuery count: " + count);
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            close(connection,statement,rs);
        }
    }

    private static void executeUpdate() throws Exception{
        Connection connection = null;
        Statement statement = null;
        int updateCount = 0;
        try {
            connection = DriverManager.getConnection(url);
            statement = connection.createStatement();
            String sql = "update user set name='啊啊啊' where id = " + new Random().nextInt(999999);
            updateCount = statement.executeUpdate(sql);
            System.out.println("executeUpdate count: " + updateCount);
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            close(connection,statement,null);
        }
    }


    private static void close(Connection connection, Statement statement, ResultSet rs) throws Exception{
        if (rs != null){
            rs.close();
        }
        if (statement != null){
            statement.close();
        }
        if (connection != null){
            connection.close();
        }
    }
}
